{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import  datetime\n",
    "import time\n",
    "import numpy as np\n",
    "from influxdb import InfluxDBClient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from influxdb import InfluxDBClient\n",
    "current_time = datetime.now()\n",
    "current_time\n",
    "dbname = \"KlineIndex\"\n",
    "\n",
    "sqlmin = \"select * from rp_month.KlineMin1 where symbol='HSI' and  time > '{}-{}-{} 09:00:00' limit 5\".format(\n",
    "        current_time.year,\n",
    "        str(current_time.month).rjust(2, '0'),\n",
    "        str(current_time.day).rjust(2, '0'))\n",
    "\n",
    "client = InfluxDBClient('192.168.3.108',8086)\n",
    "rsmin = client.query(sqlmin, database=dbname)\n",
    "df_min = pd.DataFrame(rsmin.get_points())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>preclose</th>\n",
       "      <th>symbol</th>\n",
       "      <th>value</th>\n",
       "      <th>vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-09-16T09:31:00Z</td>\n",
       "      <td>24969.35</td>\n",
       "      <td>25045.55</td>\n",
       "      <td>24969.35</td>\n",
       "      <td>25042.03</td>\n",
       "      <td>25042.03</td>\n",
       "      <td>HSI</td>\n",
       "      <td>5.784998e+09</td>\n",
       "      <td>378528958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-09-16T09:32:00Z</td>\n",
       "      <td>24960.81</td>\n",
       "      <td>24964.07</td>\n",
       "      <td>24942.46</td>\n",
       "      <td>24964.07</td>\n",
       "      <td>24969.35</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.343827e+09</td>\n",
       "      <td>112769230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-09-16T09:33:00Z</td>\n",
       "      <td>24959.89</td>\n",
       "      <td>24973.25</td>\n",
       "      <td>24947.27</td>\n",
       "      <td>24960.81</td>\n",
       "      <td>24960.81</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.069639e+09</td>\n",
       "      <td>70190359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-09-16T09:34:00Z</td>\n",
       "      <td>24906.94</td>\n",
       "      <td>24959.56</td>\n",
       "      <td>24905.41</td>\n",
       "      <td>24956.34</td>\n",
       "      <td>24959.89</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.035159e+09</td>\n",
       "      <td>85606144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-09-16T09:35:00Z</td>\n",
       "      <td>24858.80</td>\n",
       "      <td>24906.94</td>\n",
       "      <td>24857.84</td>\n",
       "      <td>24906.94</td>\n",
       "      <td>24906.94</td>\n",
       "      <td>HSI</td>\n",
       "      <td>1.062687e+09</td>\n",
       "      <td>76409116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   time     close      high       low      open  preclose  \\\n",
       "0  2021-09-16T09:31:00Z  24969.35  25045.55  24969.35  25042.03  25042.03   \n",
       "1  2021-09-16T09:32:00Z  24960.81  24964.07  24942.46  24964.07  24969.35   \n",
       "2  2021-09-16T09:33:00Z  24959.89  24973.25  24947.27  24960.81  24960.81   \n",
       "3  2021-09-16T09:34:00Z  24906.94  24959.56  24905.41  24956.34  24959.89   \n",
       "4  2021-09-16T09:35:00Z  24858.80  24906.94  24857.84  24906.94  24906.94   \n",
       "\n",
       "  symbol         value        vol  \n",
       "0    HSI  5.784998e+09  378528958  \n",
       "1    HSI  1.343827e+09  112769230  \n",
       "2    HSI  1.069639e+09   70190359  \n",
       "3    HSI  1.035159e+09   85606144  \n",
       "4    HSI  1.062687e+09   76409116  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_min.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['strategyid', 'market', 'symbol', 'klinetype', 'bstype', 'triggerprice', 'triggertime', 'issend',\n",
    "                       'confidence', 'isused', 'isright', 'addtime']\n",
    "\n",
    "data = []\n",
    "now = time.strftime(\"%Y-%m-%d %H:%M:%S\", time.localtime())\n",
    "for idx, row in df_min.iterrows():\n",
    "    bstype = \"B\" \n",
    "    data.append([1, 'HK', 'HSI','Day', bstype, row.close, now, '0', '0.0', 0, 0, now])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>strategyid</th>\n",
       "      <th>market</th>\n",
       "      <th>symbol</th>\n",
       "      <th>klinetype</th>\n",
       "      <th>bstype</th>\n",
       "      <th>triggerprice</th>\n",
       "      <th>triggertime</th>\n",
       "      <th>issend</th>\n",
       "      <th>confidence</th>\n",
       "      <th>isused</th>\n",
       "      <th>isright</th>\n",
       "      <th>addtime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>HK</td>\n",
       "      <td>HSI</td>\n",
       "      <td>Day</td>\n",
       "      <td>B</td>\n",
       "      <td>24969.35</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>HK</td>\n",
       "      <td>HSI</td>\n",
       "      <td>Day</td>\n",
       "      <td>B</td>\n",
       "      <td>24960.81</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>HK</td>\n",
       "      <td>HSI</td>\n",
       "      <td>Day</td>\n",
       "      <td>B</td>\n",
       "      <td>24959.89</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>HK</td>\n",
       "      <td>HSI</td>\n",
       "      <td>Day</td>\n",
       "      <td>B</td>\n",
       "      <td>24906.94</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>HK</td>\n",
       "      <td>HSI</td>\n",
       "      <td>Day</td>\n",
       "      <td>B</td>\n",
       "      <td>24858.80</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-09-16 10:22:51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   strategyid market symbol klinetype bstype  triggerprice  \\\n",
       "0           1     HK    HSI       Day      B      24969.35   \n",
       "1           1     HK    HSI       Day      B      24960.81   \n",
       "2           1     HK    HSI       Day      B      24959.89   \n",
       "3           1     HK    HSI       Day      B      24906.94   \n",
       "4           1     HK    HSI       Day      B      24858.80   \n",
       "\n",
       "           triggertime issend confidence  isused  isright              addtime  \n",
       "0  2021-09-16 10:22:51      0        0.0       0        0  2021-09-16 10:22:51  \n",
       "1  2021-09-16 10:22:51      0        0.0       0        0  2021-09-16 10:22:51  \n",
       "2  2021-09-16 10:22:51      0        0.0       0        0  2021-09-16 10:22:51  \n",
       "3  2021-09-16 10:22:51      0        0.0       0        0  2021-09-16 10:22:51  \n",
       "4  2021-09-16 10:22:51      0        0.0       0        0  2021-09-16 10:22:51  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pd.DataFrame(data, columns=columns)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([['1', 'HK', 'HSI', 'Day', 'B'],\n",
       "       ['24969.35', '2021-09-16 10:14:26', '0', '0.0', '0'],\n",
       "       ['0', '2021-09-16 10:14:26', '1', 'HK', 'HSI'],\n",
       "       ['Day', 'B', '24960.81', '2021-09-16 10:14:26', '0'],\n",
       "       ['0.0', '0', '0', '2021-09-16 10:14:26', '1'],\n",
       "       ['HK', 'HSI', 'Day', 'B', '24959.89'],\n",
       "       ['2021-09-16 10:14:26', '0', '0.0', '0', '0'],\n",
       "       ['2021-09-16 10:14:26', '1', 'HK', 'HSI', 'Day'],\n",
       "       ['B', '24906.94', '2021-09-16 10:14:26', '0', '0.0'],\n",
       "       ['0', '0', '2021-09-16 10:14:26', '1', 'HK'],\n",
       "       ['HSI', 'Day', 'B', '24858.8', '2021-09-16 10:14:26'],\n",
       "       ['0', '0.0', '0', '0', '2021-09-16 10:14:26']], dtype='<U32')"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = np.array(data).reshape(12,5)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
